package com.java.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.java.vo.ShopHomeTemplate;

@Repository
public class HomeTemplateDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int insertShopHomeTemplate(ShopHomeTemplate shopHomeTemplate) {
		final String sql = "INSERT INTO shop_home_template( area_name, summary, "
				+ " weight, status, created_at, updated_at)" + " VALUES ( ?, ?,   ?, ?, NOW(), NOW())";
		logger.debug("插入Templatesql： " + sql);
		logger.debug("插入参数： " + shopHomeTemplate.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopHomeTemplate.getAreaName());
					ps.setString(2, shopHomeTemplate.getSummary());
					ps.setInt(3, shopHomeTemplate.getWeight());
					ps.setBoolean(4, shopHomeTemplate.isStatus());
					return ps;
				}
			}, keyHolder);
		} catch (Exception e) {
			e.printStackTrace();
			logger.debug(e.toString());
		}
		return keyHolder.getKey().intValue();
	}

	public int UpdateShopHomeTemplate(ShopHomeTemplate shopHomeTemplate) {
		final String sql = "REPLACE INTO shop_home_template( area_name, summary, "
				+ " weight, status, created_at, updated_at, id) VALUES ( ?, ?,   ?, ?, NOW(), NOW(), ?)";
		logger.debug("更新Template  sql： " + sql);
		logger.debug("更新参数： " + shopHomeTemplate.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			 jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopHomeTemplate.getAreaName());
					ps.setString(2, shopHomeTemplate.getSummary());

					ps.setInt(3, shopHomeTemplate.getWeight());
					ps.setBoolean(4, shopHomeTemplate.isStatus());
					ps.setInt(5, shopHomeTemplate.getId());
					return ps;
				}
			}, keyHolder);
			return ((Long) keyHolder.getKeyList().get(0).get("GENERATED_KEY")).intValue();
		} catch (Exception e) {
			e.printStackTrace();
			logger.debug(e.toString());
			return -1;
		}
	}
	
	

	public ShopHomeTemplate getShopHomeTemplateById(int id) {
		final String sql = "SELECT * FROM shop_home_template  WHERE id = ? ";

		try {
			ShopHomeTemplate info = jdbcTemplate.queryForObject(sql, new Object[] { id },
					new RowMapper<ShopHomeTemplate>() {

						@Override
						public ShopHomeTemplate mapRow(ResultSet rs, int rowNum) throws SQLException {
							ShopHomeTemplate info = setShopHomeTemplate(rs);
							return info;
						}
					});

			return info;
		} catch (DataAccessException e) {
			logger.info("getShopHomeTemplateById e = " + e.getLocalizedMessage());
		}

		return null;
	}

	private ShopHomeTemplate setShopHomeTemplate(ResultSet rs) throws SQLException {
		ShopHomeTemplate info = new ShopHomeTemplate();
		info.setId(rs.getInt("id"));
		info.setAreaName(rs.getString("area_name"));
		info.setSummary(rs.getString("summary"));
		info.setWeight(rs.getInt("weight"));
		info.setStatus(rs.getBoolean("status"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		return info;
	}

	private void getShopHomeTemplateCondition(ArrayList<Object> parmList, StringBuffer sql, String areaName, String content, String summary, String weight){
		if (!StringUtils.isEmpty(areaName)) {
			sql.append(" AND area_name = ? ");
			parmList.add(areaName);
		}
		
		if (!StringUtils.isEmpty(summary)) {
			sql.append(" AND summary = ? ");
			parmList.add(summary);
		}
		
		if (!StringUtils.isEmpty(weight)) {
			sql.append(" AND weight = ? ");
			parmList.add(weight);
		}
		
		if (!StringUtils.isEmpty(content)) {
			sql.append(" AND ( area_name LIKE ? "
					+ "OR summary LIKE ? ) ");
			parmList.add("%" + content + "%");
			parmList.add("%" + content + "%");
		}
	}
	
	public List<ShopHomeTemplate> getShopHomeTemplate(String areaName, String content, String summary, String weight, Integer page) {
		ArrayList<Object> parmList = new ArrayList<Object>();
		final StringBuffer sql = new StringBuffer(" SELECT  * FROM shop_home_template WHERE 1=1 ");
		
		getShopHomeTemplateCondition(parmList,sql,areaName,content,summary,weight);
		
		if (page != null && page > 0) {
			sql.append(" ORDER BY id DESC LIMIT ").append(10 * (page - 1)).append(",").append(10);
		} else {
			sql.append(" ORDER BY id DESC");
		}
		
		System.out.println("查询产品sql： " + sql);
		System.out.println("查询参数： " + areaName + " | " + content + " | " + page);

		try {
			List<ShopHomeTemplate> users = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]),
					new ResultSetExtractor<List<ShopHomeTemplate>>() {
						public List<ShopHomeTemplate> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplate> list = new ArrayList<ShopHomeTemplate>();
							while (rs.next()) {
								ShopHomeTemplate info = setShopHomeTemplate(rs);
								list.add(info);
							}
							return list;
						}
					});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplate  = " + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public int getShopHomeTemplateCnt(String areaName, String content, String summary, String weight) {
		ArrayList<Object> parmList = new ArrayList<Object>();
		final StringBuffer sql = new StringBuffer("SELECT  count(1) cnt FROM shop_home_template WHERE 1=1 ");
		getShopHomeTemplateCondition(parmList,sql,areaName,content,summary,weight);
		
		System.out.println("查询产品sql： " + sql);
		System.out.println("查询参数： " + areaName );

		int count = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]), (rs) -> {
			Integer i = null;
			while (rs.next()) {
				i = rs.getInt("cnt");
			}
			return i;
		});
		return count;
	}

}